package com.neutch.dao.impl;

import com.neutch.dao.UserDAO;
import com.neutch.entity.User;
import com.neutch.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDAOlmpl implements UserDAO {

    @Override
    public List<User> selectAll() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<User> users = new ArrayList<>();
        String sql = "select id,user_name,user_password," +
                "user_phone,user_addr,user_createtime," +
                "user_updatetime,user_status from t_user";
        try {
            conn = DBUtil.getConnection();
            stmt = DBUtil.getstatement(conn);
            rs = DBUtil.getResultSet(stmt,sql);
            while (rs.next()){
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("user_name"));
                user.setPassword(rs.getString("user_password"));
                user.setPhone(rs.getString("user_phone"));
                user.setAdress(rs.getString("user_addr"));
                user.setCreatTime(rs.getTimestamp("user_createtime"));
                user.setUpdateTime(rs.getTimestamp("user_updatetime"));
                user.setStatus(rs.getInt("user_status"));

                 users.add(user);
            }
        }catch (Exception e){
            e.printStackTrace();
            DBUtil.rollback(conn);
        }finally {
            DBUtil.close(stmt,conn,rs);
        }
        return users;
    }

    @Override
    public User selectById(Integer id) {
        Connection conn = null;
        PreparedStatement prep = null;
        ResultSet rs = null;
        User user = null;
        String sql = "select id,user_name,user_password," +
                "user_phone,user_addr,user_createtime," +
                "user_updatetime,user_status from t_user where id = ?";
        try {
            conn = DBUtil.getConnection();
            prep = DBUtil.getprepareStatement(conn,sql);
            prep.setInt(1,id);
            rs = DBUtil.getResultSet(prep);
            if (rs.next()){
                user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("user_name"));
                user.setPassword(rs.getString("user_password"));
                user.setPhone(rs.getString("user_phone"));
                user.setAdress(rs.getString("user_addr"));
                user.setCreatTime(rs.getTimestamp("user_createtime"));
                user.setUpdateTime(rs.getTimestamp("user_updatetime"));
                user.setStatus(rs.getInt("user_status"));
            }
        }catch (Exception e){
            e.printStackTrace();
            DBUtil.rollback(conn);
        }finally {
            DBUtil.close(prep,conn,rs);
        }
        return user;
    }

    @Override
    public void insert(User user) {
        Connection conn = null;
        PreparedStatement prep = null;
        String sql = "insert into t_user values(null,?,?,?,?,now(),now(),1)";
        try{
            conn = DBUtil.getConnection();
            prep = DBUtil.getprepareStatement(conn,sql);
            prep.setString(1,user.getName());
            prep.setString(2,user.getPassword());
            prep.setString(3,user.getPhone());
            prep.setString(4,user.getAdress());

           prep.executeUpdate();

        }catch (Exception e){
            e.printStackTrace();
            DBUtil.rollback(conn);
        }finally {
            DBUtil.close(prep,conn);
        }
    }

    @Override
    public void delete(Integer id) {
        Connection conn = null;
        PreparedStatement prep = null;
        String sql = "delete from t_user where id = ?";
        try{
            conn = DBUtil.getConnection();
            prep = DBUtil.getprepareStatement(conn,sql);
            prep.setInt(1,id);


            prep.executeUpdate();

        }catch (Exception e){
            e.printStackTrace();
            DBUtil.rollback(conn);
        }finally {
            DBUtil.close(prep,conn);
        }
    }

    @Override
    public void update(User user) {
        Connection conn = null;
        PreparedStatement prep = null;
        String sql = "update t_user set user_name=?,user_password=?,user_phone=?,user_addr=?,user_updatetime=now() where id=?";
        try{
            conn = DBUtil.getConnection();
            prep = DBUtil.getprepareStatement(conn,sql);
            prep.setString(1,user.getName());
            prep.setString(2,user.getPassword());
            prep.setString(3,user.getPhone());
            prep.setString(4,user.getAdress());
            prep.setInt(5,user.getId());


            prep.executeUpdate();

        }catch (Exception e){
            e.printStackTrace();
            DBUtil.rollback(conn);
        }finally {
            DBUtil.close(prep,conn);
        }
    }
}
